const util = require('../../common/util');
const config = require('../../common/config');
const sqlite3 = require('sqlite3');

class OrdersDB{
    /**
     * 构造函数
     * @param {String} dbFile 数据库文件路径
     * @constructor
     */
    constructor() {
        this.dbFile = config.dbFile;
        this.instance = null;
        this.db = null;
    }
    // 获取数据库实例
    /**
     * @returns {OrdersDB} OrdersDB实例
     */
    static getInstance() {
        if (!this.instance) {
            try {
                this.instance = new OrdersDB();
                util.log("OrdersDB实例已创建");
            } catch (err) {
                util.err(err);
            }
        }
        
        return this.instance;
    }

    /**
     * @returns {Promise} Promise对象
     */
    async connect() {
        if (this.isConnected) return; // 避免重复连接
        return new Promise((resolve, reject) => {
            this.db = new sqlite3.Database(this.dbFile, (err) => {
                if (err) {
                    util.err('数据库连接失败', err);
                    return reject(err);
                }
                this.isConnected = true;
                util.log('数据库连接成功');
                this.db.run(``, (err) => {
                    if (err) util.err('表结构初始化警告');
                    resolve();
                });
            });

            // ✅ 连接错误监听（持续重连逻辑）
            this.db.on('error', (err) => {
                util.err('数据库连接断开', err);
                this.isConnected = false;
                // 可选：5秒后自动重连
                setTimeout(() => this.connect(), 5000);
            });
        });
    }


    //新增订单
    add(orders){
        return new Promise((resolve, reject) => {
            const sql = `
                INSERT INTO orders (
                   order_number, buyer_id, seller_id,item_id,
                    quantity, total_price, created_at, order_status
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
            `;
            const values = [
                orders.orders_number, 
                orders.buyer_id, 
                orders.seller_id, 
                orders.item_id,
                orders.quantity, 
                orders.total_price, 
                new Date().toISOString(),
                orders.order_status,
            ];
            this.db.run(sql, values, function (err) {
                if (err) {
                    reject(err);
                } else {
                    resolve(this.lastID); // 返回插入的id
                }
            });
        });
    }

    //修改订单状态
    update(order_id, order_status){
        return new Promise((resolve, reject) => {
            const sql = `UPDATE orders SET order_status = ? WHERE order_id = ?`;
            this.db.run(sql, [ order_status,order_id], function (err,result) {
                err ? reject(err) : resolve(result);
            });
        });
    }

    //通过订单id查询
    getByorderid(order_id){
        return new Promise((resolve, reject) => {
            const sql = "SELECT * FROM orders WHERE order_id = ? ";
            this.db.get(sql, order_id, (err, result) => {
                err ? reject(err) : resolve(result);
            });
        });
    }
    //通过买方id查询
    getBybuyerid(buyer_id) {
  return new Promise((resolve, reject) => {
    const sql = `
      SELECT 
        orders.*, 
        imgs.img_url AS first_img_url,
        users.nickname AS seller_nickname,
        goods.goods_name,
        goods.goods_desc
      FROM orders
      JOIN goods ON orders.item_id = goods.goods_id
      JOIN imgs ON goods.goods_id = imgs.goods_id
      JOIN users ON orders.seller_id = users.user_id
      WHERE orders.buyer_id = ?
    `;
    this.db.all(sql, buyer_id, (err, result) => {
      err ? reject(err) : resolve(result);
        });
    });
    }
    //通过卖方id查询
    getBysellerid(seller_id){
        return new Promise((resolve, reject) => {
            const sql = "SELECT * FROM orders WHERE seller_id = ? ";
            this.db.all(sql, seller_id, (err, result) => {
                err ? reject(err) : resolve(result);
            });
        });
    }

    //删除订单
    remove(order_id){
        const sql = `DELETE FROM orders WHERE order_id = ? `;
        return new Promise((resolve, reject)=>{
            this.db.run(sql, order_id ,function (err){
                err ? reject(err) : resolve(this.changes);
            })
        })
    }
}
module.exports = OrdersDB;